/**
 * @author Mie Plougstrup, Anders Abildgaard, Bo Stokholm 
 * @Version 16-12-2014
 */
package dbLayer;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.DispositionList;

public class DbDispositionList {

	private Connection con;
	private DbDisposition dbDisposition;

	/**
	 * Constructor - Initializes fields.
	 */
	public DbDispositionList() {
		con = DbConnection.getInstance().getDBcon();
		dbDisposition = new DbDisposition();
	}

	/**
	 * Method for retrieving a dispositionList by its ID
	 * 
	 * @param dispListId 		The specific ID
	 * @param associate			Retrieve associations or not.
	 * @return 					The DispositionList matching the ID
	 */
	public DispositionList retrieveById(int dispListId, boolean associate) {
		return singleWhere("dispListId = " + dispListId, associate);
	}

	/**
	 * Method for retrieving a dispositionList by its date
	 * 
	 * @param date the specific date
	 * @return The DispositionList matching the date
	 */
	public DispositionList retrieveByDate(Date date) {
		return singleWhere("shippingDate = '" + date + "'", true);
	}

	/**
	 * Method for retrieving all undone dispositionList objects.
	 * 
	 * @return The list of dispositionList objects.
	 */
	public ArrayList<DispositionList> retrieveUndone(boolean associate) {
		return multiWhere("isDone = 0 ORDER BY shippingDate", associate);
	}

	/**
	 * Method that inserts a dispositionList object into the database.
	 * 
	 * @param DispositionList dplObj 
	 * @return int ra
	 * @throws Exception If there is a problem, an exception will be thrown. This should be caught in the UI.
	 */
	public int insert(DispositionList dplObj) throws Exception {
		int ra = -1;
		String query = "Insert into dispositionList(dispListId, shippingDate, isDone) "
				+ "Values(?,?,?)";
		try {
			PreparedStatement prpstmt = con.prepareStatement(query);
			prpstmt.setInt(1, dplObj.getDispListId());
			prpstmt.setDate(2, dplObj.getShippingDate());
			if(dplObj.isDone()) {
				prpstmt.setByte(3, (byte) 1);
			} else {
				prpstmt.setByte(3, (byte) 0);
			}
			prpstmt.setQueryTimeout(5);
			ra = prpstmt.executeUpdate();
			prpstmt.close();
		} catch(Exception e) {
			throw new Exception("DispositionList not inserted correct");
		}
		return ra;
	}	

	/**
	 * Method for updating a dispositionList in the database.
	 * 
	 * @param dispositionList 
	 * @return
	 */
	public int update(DispositionList dispositionList) {
		int ra = -1;

		String query = "Update DispositionList SET"
				+ " shippingDate ='" + dispositionList.getShippingDate()
				+ "', isDone =";
		if(dispositionList.isDone()) {
			query = query + 1;
		} else {
			query = query + 0;
		}
		query = query + " WHERE dispListId =" + dispositionList.getDispListId();
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {}
		return ra;
	}

	/**
	 * Method for deleting a dispositionList from the database.
	 * 
	 * @param DispositionList dispositionList
	 * @return int ra
	 */
	public int delete(DispositionList dispositionList) {
		int ra = -1;

		String query = "Delete from dispositionList WHERE dispListId = " + dispositionList.getDispListId();
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {}
		return ra;
	}

	/**
	 * Method for creating a single dispositionList object from the database.
	 * 
	 * @param String wClause		The wclause, stating which object to be created.
	 * @param boolean 				retrieveAssociation
	 * @return 						DispositionList object
	 */
	private DispositionList singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		DispositionList dlObj = new DispositionList();

		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				dlObj = buildDispList(results);
				stmt.close();

				if (retrieveAssociation) {
					dlObj.setDispCollection(dbDisposition.retrieveByDispList(dlObj.getDispListId(), true));
				}
			}
		} catch (Exception e) {}
		return dlObj;
	}

	/**
	 * Method for creating multiple dispositionList objects from the database.
	 * 
	 * @param String wClause The wClause, stating which objects to be created.
	 * @param boolean retrieveAssociation
	 * @return ArrayList<DispositionList> list
	 */
	private ArrayList<DispositionList> multiWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<DispositionList> dlList = new ArrayList<DispositionList>();

		String query = buildQuery(wClause);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()) {
				dlList.add(buildDispList(results));
			}
			stmt.close();
			if(retrieveAssociation) {
				for(DispositionList dlObj: dlList) {
					dlObj.setDispCollection(dbDisposition.retrieveByDispList(dlObj.getDispListId(), true));
				}
			}
		} catch(Exception e) {}
		return dlList;
	}

	/**
	 * Method for building a query string with SQL syntax.
	 * 
	 * @param String 		wClause
	 * @return 				String query
	 */
	private String buildQuery(String wClause) {
		String query = "SELECT dispListId, shippingDate, isDone from DispositionList";

		if (wClause.length() > 0) {
			query = query + " WHERE " + wClause;
		}
		return query;
	}

	/**
	 * Method for building a dispositionList object.
	 * 
	 * @param results 		A result set containing the required information from the database.
	 * @return 				DispositionList object
	 */
	private DispositionList buildDispList(ResultSet results) {
		DispositionList dlObj = new DispositionList();
		try {
			dlObj.setDispListId(results.getInt("dispListId"));
			dlObj.setShippingDate(results.getDate("shippingDate"));
			dlObj.setDone(results.getBoolean("isDone"));
		} catch (Exception e) {}
		return dlObj;
	}
	
}
